Create database Trabalho_Final;
use Trabalho_Final;

#CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminSenha';
#GRANT ALL PRIVILEGES ON Trabalho_Final.* TO 'admin'@'localhost';

#CREATE USER 'user'@'localhost' IDENTIFIED BY 'UserSenha';
#GRANT SELECT, INSERT ON Trabalho_Final.* TO 'user'@'localhost';
#FLUSH PRIVILEGES;

CREATE TABLE FUNCIONARIOS (
    cpf DECIMAL(11) NOT NULL,
    nome VARCHAR(100) NOT NULL,
    telefone VARCHAR(20) NOT NULL,
    salario DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_funcionarios PRIMARY KEY (cpf)
);

CREATE TABLE CLIENTES (
    cpf_cnpj DECIMAL(14) NOT NULL,
    nome VARCHAR(100) NOT NULL,
    telefone VARCHAR(20) NOT NULL,
    endereco VARCHAR(150) NOT NULL,
    CONSTRAINT pk_clientes PRIMARY KEY (cpf_cnpj)
);

CREATE TABLE FORNECEDORES (
    cnpj DECIMAL(14) NOT NULL,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    telefone VARCHAR(20) NOT NULL,
    endereco VARCHAR(150) NOT NULL,
    CONSTRAINT pk_fornecedores PRIMARY KEY (cnpj)
);

CREATE TABLE GALPOES (
    num_galpao INT NOT NULL,
    localizacao VARCHAR(100) NOT NULL,
    capacidade INT NOT NULL,
    CONSTRAINT pk_galpoes PRIMARY KEY (num_galpao)
);


CREATE TABLE ENTREGADOR (
    cpf DECIMAL(11) NOT NULL,
    CONSTRAINT pk_entregador PRIMARY KEY (cpf),
    CONSTRAINT fk_entregador_funcionario FOREIGN KEY (cpf)
        REFERENCES FUNCIONARIOS (cpf)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE REPOSITORES (
    cpf DECIMAL(11) NOT NULL,
    CONSTRAINT pk_repositores PRIMARY KEY (cpf),
    CONSTRAINT fk_repositores_funcionario FOREIGN KEY (cpf)
        REFERENCES FUNCIONARIOS (cpf)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE VENDEDORES (
    cpf DECIMAL(11) NOT NULL,
    modalidade  ENUM('I','E') NOT NULL,
    CONSTRAINT pk_vendedores PRIMARY KEY (cpf),
    CONSTRAINT fk_vendedores_funcionario FOREIGN KEY (cpf)
        REFERENCES FUNCIONARIOS (cpf)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE PEDIDOS (
    id_pedido INT NOT NULL AUTO_INCREMENT,
    data DATE NOT NULL,
    status ENUM('Realizado','Pago','Em Transporte','Entregue','Cancelado') NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    cpf_cliente DECIMAL(14) NOT NULL,
    cpf_vendedor DECIMAL(11) NOT NULL,
    CONSTRAINT pk_pedidos PRIMARY KEY (id_pedido),
    CONSTRAINT fk_pedidos_cliente FOREIGN KEY (cpf_cliente)
        REFERENCES CLIENTES (cpf_cnpj)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT fk_pedidos_vendedor FOREIGN KEY (cpf_vendedor)
        REFERENCES VENDEDORES (cpf)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE PRODUTOS (
    id_produto INT NOT NULL AUTO_INCREMENT,
    sku VARCHAR(20) NOT NULL,
    nome VARCHAR(100) NOT NULL,
    descricao MEDIUMTEXT NOT NULL,
    preco DECIMAL(10,2) NOT NULL,
    cnpj_fornecedor DECIMAL(14) NOT NULL,
    CONSTRAINT pk_produtos PRIMARY KEY (id_produto),
    CONSTRAINT chk_preco
        CHECK (preco >= 0),
    CONSTRAINT fk_produtos_fornecedor FOREIGN KEY (cnpj_fornecedor)
        REFERENCES FORNECEDORES (cnpj)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE ENTREGA (
    id_entrega INT NOT NULL AUTO_INCREMENT,
    data_saida DATE NOT NULL,
    data_entrega DATE,
    id_pedido INT NOT NULL,
    cpf_entregador DECIMAL(11) NOT NULL,
    CONSTRAINT pk_entrega PRIMARY KEY (id_entrega),
    CONSTRAINT fk_entrega_pedido FOREIGN KEY (id_pedido)
        REFERENCES PEDIDOS (id_pedido)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT fk_entrega_entregador FOREIGN KEY (cpf_entregador)
        REFERENCES ENTREGADOR (cpf)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE ROTA_ENTREGA (
    id_rota INT NOT NULL AUTO_INCREMENT,
    descricao VARCHAR(150) NOT NULL,
    distancia DECIMAL(10,2) NOT NULL,
    endereco_destino VARCHAR(150) NOT NULL,
    id_entrega INT NOT NULL,
    CONSTRAINT pk_rota_entrega PRIMARY KEY (id_rota),
    CONSTRAINT fk_rota_entrega_entrega FOREIGN KEY (id_entrega)
        REFERENCES ENTREGA (id_entrega)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE PEDIDO_PRODUTOS (
    id_pedido INT NOT NULL,
    id_produto INT NOT NULL,
    quantidade INT NOT NULL,
    preco_unitario  DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_pedido_produtos PRIMARY KEY (id_pedido, id_produto),
    CONSTRAINT fk_pedidoprodutos_pedido FOREIGN KEY (id_pedido)
        REFERENCES PEDIDOS (id_pedido)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_pedidoprodutos_produto FOREIGN KEY (id_produto)
        REFERENCES PRODUTOS (id_produto)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
	CONSTRAINT chk_preco_unitario
        CHECK (preco_unitario >= 0)
);

CREATE TABLE ESTOQUE (
    id_estoque INT NOT NULL AUTO_INCREMENT,
    qtd_disponivel INT NOT NULL,
    num_galpao INT NOT NULL,
    id_produto INT NOT NULL,
    CONSTRAINT pk_estoque PRIMARY KEY (id_estoque),
    CONSTRAINT fk_estoque_galpao FOREIGN KEY (num_galpao)
        REFERENCES GALPOES (num_galpao)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT fk_estoque_produto FOREIGN KEY (id_produto)
        REFERENCES PRODUTOS (id_produto)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE PAGAMENTOS (
    id_pagamento INT NOT NULL AUTO_INCREMENT,
    forma ENUM('Dinheiro','Cartao','Pix','Boleto') NOT NULL,
    data_pagamento  DATE NOT NULL,
    valor DECIMAL(10,2) NOT NULL,
    id_pedido INT NOT NULL,
    CONSTRAINT pk_pagamentos PRIMARY KEY (id_pagamento),
    CONSTRAINT fk_pagamentos_pedido FOREIGN KEY (id_pedido)
        REFERENCES PEDIDOS (id_pedido)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

CREATE TABLE ACESSA (
    cpf_repositor DECIMAL(11) NOT NULL,
    num_galpao INT NOT NULL,
    chave_galpao BOOLEAN,
    CONSTRAINT pk_acessa PRIMARY KEY (cpf_repositor, num_galpao),
    CONSTRAINT fk_acessa_repositor FOREIGN KEY (cpf_repositor)
        REFERENCES REPOSITORES (cpf)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_acessa_galpao FOREIGN KEY (num_galpao)
        REFERENCES GALPOES (num_galpao)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

INSERT INTO FUNCIONARIOS (cpf, nome, telefone, salario) VALUES
(12345678901, 'Ana Souza', '47988110022', 3200.00),
(23456789012, 'Bruno Lima', '47988110023', 3150.00),
(34567890123, 'Carla Mendes', '47988110024', 3300.00),
(45678901234, 'Diego Alves', '47988110025', 3100.00),
(56789012345, 'Elisa Rocha', '47988110026', 3250.00),
(67890123456, 'Fabio Nunes', '42988110027', 3180.00),
(78901234567, 'Gabriela Dias', '47988110028', 2900.00),
(89012345678, 'Heitor Cruz', '47988110029', 2950.00),
(90123456789, 'Isabela Ramos', '47988110030', 3000.00),
(10234567890, 'Joao Pedro Castro', '47988110031', 2980.00),
(11234567891, 'Karina Farias', '42988110032', 3050.00),
(12234567892, 'Lucas Barros', '47988110033', 2990.00),
(13234567893, 'Mariana Teixeira', '47988110034', 4200.00),
(14234567894, 'Nicolas Pinto', '47988110035', 4100.00),
(15234567895, 'Olivia Santos', '47988110036', 4300.00),
(16234567896, 'Pedro Henrique Silva', '42988110037', 4150.00),
(17234567897, 'Queila Moraes', '47988110038', 4250.00),
(18234567898, 'Rafael Costa', '47988110039', 4180.00);


INSERT INTO CLIENTES (cpf_cnpj, nome, telefone, endereco) VALUES
(21345678901, 'Fernanda Oliveira', '47991112222', 'Rua XV de Novembro, 450 - Sao Bento do Sul/SC'),
(32345678902, 'Marcos Vinicius', '47991112223', 'Rua Felipe Schmidt, 120 - Rio Negrinho/SC'),
(12345678000199, 'Comercio Silva Ltda', '4730001111', 'Av. Getulio Vargas, 890 - Mafra/SC'),
(43345678903, 'Patricia Gomes', '47991112224', 'Rua Vidal Ramos, 300 - Canoinhas/SC'),
(54345678904, 'Rodrigo Martins', '47991112225', 'Rua Curitiba, 210 - Papanduva/SC'),
(98765432000188, 'Distribuidora Nova Ltda', '4230002222', 'Av. Cel. Amazonas, 560 - Uniao da Vitoria/PR');

INSERT INTO ENTREGADOR (cpf) VALUES
(12345678901),
(23456789012),
(34567890123),
(45678901234),
(56789012345),
(67890123456);

INSERT INTO REPOSITORES (cpf) VALUES
(78901234567),
(89012345678),
(90123456789),
(10234567890),
(11234567891),
(12234567892);

INSERT INTO VENDEDORES (cpf, modalidade) VALUES
(13234567893, 'I'),
(14234567894, 'E'),
(15234567895, 'I'),
(16234567896, 'E'),
(17234567897, 'I'),
(18234567898, 'E');

INSERT INTO FORNECEDORES (cnpj, nome, email, telefone, endereco) VALUES
(11111111000111, 'Bipack Embalagens Flexiveis Ltda', 'bipack@embalagens.com.br', '4733003333', 'Rua Blumenau, 850 - Joinville/SC'),
(22222222000122, 'Green Enviroment Papel e Papelao Ltda', 'vendas@greenenviroment.com.br', '4733004444', 'Rua Ottokar Doerffel, 220 - Joinville/SC'),
(33333333000133, 'Infusion Embalagens Industriais Ltda', 'comercial@infusion.com.br', '4133005555', 'Rua Marechal Deodoro, 1200 - Curitiba/PR'),
(44444444000144, 'Packaging Solucoes em Plastico Ltda', 'comercial@packaging.com.br', '4133006666', 'Av. Sete de Setembro, 3400 - Curitiba/PR'),
(55555555000155, 'Box Box Caixas e Displays Ltda', 'contato@boxbox.com.br', '4733007777', 'Rua Jose Vieira, 78 - Jaragua do Sul/SC'),
(66666666000166, 'Crep Fitas e Adesivos Industriais Ltda', 'crepadesivas@gmail.com.br', '4233008888', 'Rua Coronel Amazonas, 145 - Uniao da Vitoria/PR');

INSERT INTO PRODUTOS (sku, nome, descricao, preco, cnpj_fornecedor) VALUES
('SKU001', 'Caixa de Papelao Ondulado N.3', 'Caixa de papelao ondulado reforcado, medidas 40x30x30cm, resistencia para transporte', 8.90, 22222222000122),
('SKU002', 'Filme Stretch 20 micras', 'Bobina de filme stretch para paletizacao, 500mm x 300m, alta elasticidade', 45.90, 11111111000111),
('SKU003', 'Fita Adesiva Transparente 48mm', 'Fita adesiva para fechamento de caixas, rolo com 100m, alta aderencia', 12.50, 66666666000166),
('SKU004', 'Saco Plastico Biodegradavel 40x60', 'Saco plastico biodegradavel para embalagem industrial, pacote com 100 unidades', 34.90, 44444444000144),
('SKU005', 'Envelope de Seguranca Kraft', 'Envelope kraft para envio de documentos e pequenos volumes, pacote com 50 unidades', 22.90, 22222222000122),
('SKU006', 'Etiqueta Adesiva Termica 100x150mm', 'Rolo de etiquetas termicas para identificacao de encomendas, 1000 unidades', 39.90, 33333333000133),
('SKU007', 'Caixa de Papelao para Mudanca G', 'Caixa reforcada para mudancas, medidas 60x40x40cm, alta capacidade de carga', 14.90, 22222222000122),
('SKU008', 'Plastico Bolha 100m', 'Bobina de plastico bolha para protecao de itens frageis, largura 1m', 89.90, 11111111000111),
('SKU009', 'Cinta de Arquear Poliester 19mm', 'Cinta de poliester para amarração e fixação de cargas em paletes, bobina 1000m', 129.90, 33333333000133),
('SKU010', 'Display de Papelao para Ponto de Venda', 'Display personalizado de papelao para exposicao de produtos em loja', 65.00, 55555555000155),
('SKU011', 'Selo de Seguranca Inviolavel', 'Selo adesivo inviolavel para lacre de caixas e volumes, pacote com 200 unidades', 28.50, 66666666000166);

INSERT INTO GALPOES (num_galpao, localizacao, capacidade) VALUES
(1, 'Galpao Matriz - Sao Bento do Sul/SC', 8000),
(2, 'Galpao Distribuicao - Mafra/SC', 6000),
(3, 'Galpao Apoio Regional - Curitiba/PR', 5000);

INSERT INTO ESTOQUE (qtd_disponivel, num_galpao, id_produto) VALUES
(200, 1, 1),   
(150, 2, 1),
(80, 1, 2),  
(60, 3, 2),
(300, 1, 3),   
(120, 2, 4),  
(90, 1, 5),   
(70, 2, 6),   
(50, 1, 7),   
(40, 3, 8),    
(35, 3, 9),   
(25, 2, 10),   
(100, 1, 11);  

INSERT INTO ACESSA (cpf_repositor, num_galpao, chave_galpao) VALUES
(78901234567, 1, true),
(89012345678, 1, false),
(90123456789, 2, false),
(10234567890, 2, true),
(11234567891, 3, true),
(12234567892, 3, true);

INSERT INTO PEDIDOS (data, status, total, cpf_cliente, cpf_vendedor) VALUES
('2026-01-05', 'Entregue', 17.80, 21345678901, 13234567893),
('2026-02-10', 'Entregue', 45.90, 32345678902, 14234567894),
('2026-03-15', 'Em Transporte', 250.00, 12345678000199, 15234567895),
('2026-04-20', 'Pago', 104.70, 43345678903, 16234567896),
('2026-05-02', 'Realizado', 114.50, 54345678904, 17234567897),
('2026-06-18', 'Cancelado', 399.00, 98765432000188, 18234567898),
('2026-07-01', 'Entregue', 59.60, 21345678901, 13234567893),
('2026-07-03', 'Pago', 179.80, 32345678902, 14234567894),
('2026-07-05', 'Em Transporte', 389.70, 12345678000199, 15234567895),
('2026-07-08', 'Realizado', 390.00, 43345678903, 16234567896),
('2026-07-10', 'Entregue', 427.50, 54345678904, 17234567897),
('2026-07-12', 'Pago', 820.00, 98765432000188, 18234567898);

INSERT INTO ENTREGA (data_saida, data_entrega, id_pedido, cpf_entregador) VALUES
('2026-01-06', '2026-01-08', 1, 12345678901),
('2026-02-11', '2026-02-13', 2, 23456789012),
('2026-03-16', NULL, 3, 34567890123),
('2026-04-21', '2026-04-23', 4, 45678901234),
('2026-05-03', '2026-05-05', 5, 56789012345),
('2026-06-19', NULL, 6, 67890123456),
('2026-07-02', '2026-07-04', 7, 12345678901),
('2026-07-04', '2026-07-06', 8, 23456789012),
('2026-07-06', NULL, 9, 34567890123),
('2026-07-09', '2026-07-11', 10, 45678901234),
('2026-07-11', '2026-07-13', 11, 56789012345),
('2026-07-13', '2026-07-15', 12, 67890123456);

INSERT INTO ROTA_ENTREGA (descricao, distancia, endereco_destino, id_entrega) VALUES
('Rota Centro - Sao Bento do Sul', 6.50, 'Rua XV de Novembro, 450 - Sao Bento do Sul/SC', 1),
('Rota Rio Negrinho', 18.20, 'Rua Felipe Schmidt, 120 - Rio Negrinho/SC', 2),
('Rota BR-280 Mafra', 45.00, 'Av. Getulio Vargas, 890 - Mafra/SC', 3),
('Rota Canoinhas', 65.00, 'Rua Vidal Ramos, 300 - Canoinhas/SC', 4),
('Rota Papanduva', 85.30, 'Rua Curitiba, 210 - Papanduva/SC', 5),
('Rota Divisa Uniao da Vitoria', 110.00, 'Av. Cel. Amazonas, 560 - Uniao da Vitoria/PR', 6),
('Rota Centro - Sao Bento do Sul', 6.50, 'Rua XV de Novembro, 450 - Sao Bento do Sul/SC', 7),
('Rota Rio Negrinho', 18.20, 'Rua Felipe Schmidt, 120 - Rio Negrinho/SC', 8),
('Rota BR-280 Mafra', 45.00, 'Av. Getulio Vargas, 890 - Mafra/SC', 9),
('Rota Canoinhas', 65.00, 'Rua Vidal Ramos, 300 - Canoinhas/SC', 10),
('Rota Papanduva', 85.30, 'Rua Curitiba, 210 - Papanduva/SC', 11),
('Rota Divisa Uniao da Vitoria', 110.00, 'Av. Cel. Amazonas, 560 - Uniao da Vitoria/PR', 12);

INSERT INTO PEDIDO_PRODUTOS (id_pedido, id_produto, quantidade, preco_unitario) VALUES
(1, 1, 2, 8.90),
(2, 2, 1, 45.90),
(3, 3, 20, 12.50),
(4, 4, 3, 34.90),
(5, 5, 5, 22.90),
(6, 6, 10, 39.90),
(7, 7, 4, 14.90),
(8, 8, 2, 89.90),
(9, 9, 3, 129.90),
(10, 10, 6, 65.00),
(11, 11, 15, 28.50),
(12, 1, 50, 8.90),
(12, 3, 30, 12.50);

INSERT INTO PAGAMENTOS (forma, data_pagamento, valor, id_pedido) VALUES
('Cartao', '2026-01-05', 17.80, 1),
('Pix', '2026-02-10', 45.90, 2),
('Boleto', '2026-03-15', 250.00, 3),
('Cartao', '2026-04-20', 104.70, 4),
('Dinheiro', '2026-05-02', 114.50, 5),
('Pix', '2026-06-18', 399.00, 6),
('Cartao', '2026-07-01', 59.60, 7),
('Boleto', '2026-07-03', 179.80, 8),
('Pix', '2026-07-05', 389.70, 9),
('Cartao', '2026-07-08', 390.00, 10),
('Dinheiro', '2026-07-10', 427.50, 11),
('Boleto', '2026-07-12', 820.00, 12);